package com.penghaisoft.microwms.common.util;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import lombok.extern.slf4j.Slf4j;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @Description EXCEl导出的公共方法
 * @author luoteng
 * @date 2017年5月24日 下午4:20:27
 * @version 1.0
 */
@Slf4j
public class ExcelExpUtil {

	/**
	 * @Title generateWorkbook
	 * @Description 生成excel
	 * @author luoteng
	 * @date 2017年5月24日 下午4:20:46
	 * @param dataList
	 *            数据列表,可以为List<MdProduct>(适用于自己查询出的结果数据),
	 *            List<Map>(公共方法查询出的结果数据)
	 * @param columnList
	 *            EXCEL的标题、注释等 List<Map> 例如[{columnComment:'产品代码',
	 *            columnName:'product_code',
	 *            columnBeanName:'productCode'}，{...}]
	 * @return SXSSFWorkbook
	 */
	public static SXSSFWorkbook generateWorkbook(List dataList,
			List<Map> columnList) {
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
		SXSSFWorkbook sw = null;
		sw = new SXSSFWorkbook(xssfWorkbook, 5000);
		Sheet sheet = sw.createSheet();

		CellStyle titleStyle = TitleStyle(xssfWorkbook);
		CellStyle contentStyle = ContentStyle(xssfWorkbook);
		CellStyle contentStyleDate = contentStyleDate(xssfWorkbook);

		Row titleRow = sheet.createRow(0);
//		Row commentsRow = sheet.createRow(1);
		int cellNum = 0;

		for (int i = 0; i < columnList.size(); i++) {
			Map map = columnList.get(i);
			Cell titleCell = titleRow.createCell(cellNum);
//			Cell commentsCell = commentsRow.createCell(cellNum);
			titleCell.setCellStyle(titleStyle);
			titleCell.setCellValue(map.get("columnComment").toString());
//			commentsCell.setCellStyle(titleStyle);
//			commentsCell.setCellValue(map.get("columnName").toString());
			cellNum++;
		}

		//int rowNum = 2;
		int rowNum = 1;

		for (int i = 0; i < dataList.size(); i++) {
			Row dataRow = sheet.createRow(rowNum++);
			Object object = dataList.get(i);
			int num = 0;
			if (object instanceof Map) {
				Map map = (Map) object;
				Iterator entries = map.entrySet().iterator();

				while (entries.hasNext()) {
					Entry entry = (Entry) entries.next();
					Cell cell = dataRow.createCell(num++);
					cell.setCellStyle(contentStyle);
					cell.setCellValue(entry.getValue().toString());
				}
			} else {
				Class cl = object.getClass();
				for (int j = 0; j < columnList.size(); j++) {
					Map map = columnList.get(j);
					try {
						String fieldName = map.get("columnBeanName").toString();
						Field field = cl.getDeclaredField(fieldName);
						field.setAccessible(true);
						String val = "";
						if (field.get(object)==null) {
							val = "";
						}else{
							String fieldType = field.getGenericType().toString();
							if(fieldType.equals("class java.util.Date")){
								SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
								val = df.format(field.get(object));
							}else if(fieldType.equals("class java.math.BigDecimal")){
								val = field.get(object).toString();
								if(val.indexOf(".") > 0){
									val = val.replaceAll("0+?$", "");
									val = val.replaceAll("[.]$", "");
								}
							}else{
								val = field.get(object).toString();
							}
						}
						Cell cell = dataRow.createCell(num++);
						cell.setCellStyle(contentStyle);
						cell.setCellValue(val);
					} catch (NoSuchFieldException e) {
//						log.info("columnList的参数在当前类内不存在，查询其父类！");
						try {
							Class superCls = cl.getSuperclass();
							Field field = superCls.getDeclaredField(map.get("columnBeanName").toString());
							field.setAccessible(true);
							String val = "";
							if (field.get(object)==null) {
								val = "";
							}else{
								String fieldType = field.getGenericType().toString();
								if(fieldType.equals("class java.util.Date")){
									SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
									val = df.format(field.get(object));
								}else if(fieldType.equals("class java.math.BigDecimal")){
									val = field.get(object).toString();
									if(val.indexOf(".") > 0){
										val = val.replaceAll("0+?$", "");
										val = val.replaceAll("[.]$", "");
									}
								}else{
									val = field.get(object).toString();
								}
							}
							Cell cell = dataRow.createCell(num++);
							cell.setCellStyle(contentStyle);
							cell.setCellValue(val);
						} catch (NoSuchFieldException e1) {
							log.info("columnList的参数在父类中不存在！");
							e1.printStackTrace();
						} catch (SecurityException e1) {
							e1.printStackTrace();
						} catch (IllegalArgumentException e1) {
							e1.printStackTrace();
						} catch (IllegalAccessException e1) {
							e1.printStackTrace();
						}
//						e.printStackTrace();
					} catch (IllegalAccessException e) {
						log.info("columnList的参数在dataList内不存在！");
						e.printStackTrace();
					}
				}
			}
		}

		return sw;
	}

	/**
	 * @Title TitleStyle
	 * @Description 设置excel标题样式
	 * @author luoteng
	 * @date 2017年5月24日 下午4:21:37
	 * @param xssfWorkbook
	 * @return CellStyle
	 */
	public static CellStyle TitleStyle(XSSFWorkbook xssfWorkbook) {
		// 设置标题样式
		CellStyle styleTitle = xssfWorkbook.createCellStyle();
		styleTitle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		styleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 设置字体
		Font font = xssfWorkbook.createFont();
		font.setFontName("微软雅黑");
		font.setFontHeightInPoints((short) 10);// 字体大小
		styleTitle.setFont(font);
		// 边框
		styleTitle.setAlignment(HSSFCellStyle.ALIGN_GENERAL);
		styleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		styleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		styleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		styleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		return styleTitle;
	}

	/**
	 * @Title ContentStyle
	 * @Description 设置excel内容样式
	 * @author luoteng
	 * @date 2017年5月24日 下午4:21:45
	 * @param xssfWorkbook
	 * @return CellStyle
	 */
	public static CellStyle ContentStyle(XSSFWorkbook xssfWorkbook) {
		// 设置标题样式
		CellStyle ContentStyle = xssfWorkbook.createCellStyle();
		// 设置字体
		Font font = xssfWorkbook.createFont();
		font.setFontName("微软雅黑");
		font.setFontHeightInPoints((short) 10);// 字体大小
		ContentStyle.setFont(font);
		// 边框
		ContentStyle.setAlignment(HSSFCellStyle.ALIGN_GENERAL);
		ContentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		ContentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		ContentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		ContentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		return ContentStyle;
	}
	
	public static CellStyle contentStyleDate(XSSFWorkbook xssfWorkbook) {
		// 设置标题样式
		CellStyle ContentStyle = xssfWorkbook.createCellStyle();
		XSSFDataFormat format= xssfWorkbook.createDataFormat();
		ContentStyle.setDataFormat(format.getFormat("yyyy年m月d日"));
		
		// 设置字体
		Font font = xssfWorkbook.createFont();
		font.setFontName("微软雅黑");
		font.setFontHeightInPoints((short) 10);// 字体大小
		ContentStyle.setFont(font);
		// 边框
		ContentStyle.setAlignment(HSSFCellStyle.ALIGN_GENERAL);
		ContentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		ContentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		ContentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		ContentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		return ContentStyle;
	}
}
